package com.cwd.arm.inter.util;

import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.cwd.arm.application.dto.importExccel.ProjectImportDto;
import com.cwd.arm.application.dto.importExccel.ReceiveNodeImportDto;
import com.cwd.arm.application.dto.importExccel.ReceiveRecordImportDto;
import com.cwd.arm.application.dto.importExccel.TaxImportDto;
import com.cwd.arm.common.enums.*;
import com.cwd.arm.common.enums.importTitle.ProjectImportTitleEnum;
import com.cwd.arm.common.exception.ArmException;
import com.cwd.arm.common.util.BigDecimalUtil;
import com.cwd.arm.common.util.DateUtil;
import com.cwd.arm.common.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

/**
 * Created by yan on 22-4-10.
 */
@Slf4j
public class ImportUtil {

    private static final int PROJECT_TITLE_SIZE = 32;

    private static final int PROJECT_TITLE_START_ROW = 1;

    private static Map<String, Integer> PROJECT_TITLE_MAP = null;

    private static final int PROJECT_DATA_START_ROW = 2;

    private static final BigDecimal DESIGN_TAX_RATE = new BigDecimal("0.06");

    public static List<ProjectImportDto> getProjectImportDtoList(MultipartFile file) throws Exception {
        Workbook workbook = getWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);
        setProjectTitleMap(sheet.getRow(PROJECT_TITLE_START_ROW));
        int startRow = PROJECT_DATA_START_ROW;
        int endRow = sheet.getPhysicalNumberOfRows() - 1;
        List<ProjectImportDto> projectImportDtoList = new ArrayList<>(endRow);
        while (endRow > startRow) {
            int projectTypeRowSize = getRowSize(sheet, ProjectImportTitleEnum.NO, startRow);
            ProjectImportDto projectImportDto = getProjectImportDto(sheet, startRow);
            int taxStartRow = startRow;
            int taxEndRow = startRow + projectTypeRowSize - 1;
            List<ReceiveNodeImportDto> receiveNodeImportDtoList = new ArrayList<>();
            while (taxEndRow >= taxStartRow) {
                int taxRowSize = getRowSize(sheet, ProjectImportTitleEnum.TAX_DATE, taxStartRow);
                String nodeName = getCellValue(sheet, ProjectImportTitleEnum.NODE_NAME, taxStartRow);
                String taxDateStr = getCellValue(sheet, ProjectImportTitleEnum.TAX_DATE, taxStartRow);
                validNode(nodeName, taxDateStr, taxStartRow);
                if (StringUtils.isNotEmpty(taxDateStr)) {
                    ReceiveNodeImportDto receiveNodeImportDto = getReceiveNodeImportDto(sheet, taxStartRow, nodeName, taxDateStr);
                    TaxImportDto taxImportDto = getRaxImportDto(sheet, taxStartRow, projectImportDto, receiveNodeImportDto);
                    List<TaxImportDto> taxImportDtoList = new ArrayList<>();
                    int receiveRecordStartRow = taxStartRow;
                    int receiveRecordEndRow = taxStartRow + taxRowSize - 1;
                    List<ReceiveRecordImportDto> receiveRecordImportDtoList = new ArrayList<>();
                    while (receiveRecordEndRow >= receiveRecordStartRow){
                        String receiveRecordDateStr = getCellValue(sheet, ProjectImportTitleEnum.RECEIVE_DATE, receiveRecordStartRow);
                        if(StringUtils.isNotEmpty(receiveRecordDateStr)){
                            ReceiveRecordImportDto receiveRecordImportDto = getReceiveRecordImportDto(sheet,receiveRecordStartRow, receiveRecordDateStr);
                            receiveRecordImportDtoList.add(receiveRecordImportDto);
                        }
                        receiveRecordStartRow +=1;
                    }
                    taxImportDto.setReceiveRecordImportDtoList(receiveRecordImportDtoList);
                    taxImportDtoList.add(taxImportDto);
                    receiveNodeImportDto.setTaxImportDtoList(taxImportDtoList);
                    receiveNodeImportDtoList.add(receiveNodeImportDto);
                }
                taxStartRow += taxRowSize;
            }
            projectImportDto.setReceiveNodeImportDtoList(receiveNodeImportDtoList);
            projectImportDtoList.add(projectImportDto);
            startRow += projectTypeRowSize;
        }
        return projectImportDtoList;
    }

    private static ReceiveRecordImportDto getReceiveRecordImportDto(Sheet sheet, int receiveRecordRow, String receiveRecordDateStr) {
        ReceiveRecordImportDto receiveRecordImportDto = new ReceiveRecordImportDto();
        try {
            Date receiveRecordDate = DateUtil.strToDate(receiveRecordDateStr.replace(".", "-"));
            receiveRecordImportDto.setReceiveDate(receiveRecordDate);
        } catch (Exception e) {
            log.warn("ImportUtil:{}", e.getMessage(), e);
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(receiveRecordRow) + "'行'"
                    + ProjectImportTitleEnum.RECEIVE_DATE.getCode() + "'请输入yyyy.mm.dd格式的日期");
        }
        // TODO: 22-4-16 确定是否要展示在excel上
        receiveRecordImportDto.setPayType(PayTypeEnum.CASH.getCode());
        String receiveAmountStr = getRequiredCellValue(sheet, receiveRecordRow, ProjectImportTitleEnum.RECEIVE_AMOUNT);
        if (StringUtil.isNumeric(receiveAmountStr)) {
            BigDecimal receiveAmount = new BigDecimal(receiveAmountStr);
            receiveRecordImportDto.setAmount(BigDecimalUtil.convertYToW(receiveAmount));
        } else {
            log.warn("ImportUtil.,请输入数字:{}",receiveAmountStr);
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(receiveRecordRow) + "'行'"
                    + ProjectImportTitleEnum.RECEIVE_AMOUNT.getCode() + "'请输入数字");
        }
        return receiveRecordImportDto;
    }

    private static TaxImportDto getRaxImportDto(Sheet sheet, int startRow, ProjectImportDto projectImportDto, ReceiveNodeImportDto receiveNodeImportDto) {
        TaxImportDto taxImportDto = new TaxImportDto();
        String taxNo = getCellValue(sheet, ProjectImportTitleEnum.TAX_NO, startRow);
        if(StringUtils.isEmpty(taxNo)){
            taxNo = "sys-tax-no-"+DateUtil.dateToDateStr(receiveNodeImportDto.getEndDate());
        }else{
            taxNo = StringUtil.toNorNumeric(taxNo);
        }
        taxImportDto.setTaxNo(taxNo);
        taxImportDto.setTaxDate(receiveNodeImportDto.getEndDate());
        taxImportDto.setTaxAmount(receiveNodeImportDto.getReceivableAmount());
        taxImportDto.setTaxRate(projectImportDto.getTaxPoint());
        return taxImportDto;
    }

    private static ReceiveNodeImportDto getReceiveNodeImportDto(Sheet sheet, int taxStartRow, String nodeName, String taxDateStr) {
        ReceiveNodeImportDto receiveNodeImportDto = new ReceiveNodeImportDto();
        receiveNodeImportDto.setNodeName(nodeName + "-" + taxDateStr);
        String nodeTypeCode = NodeTypeEnum.getCode(nodeName);
        if (nodeTypeCode == null) {
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(taxStartRow) + "'行'"
                    + ProjectImportTitleEnum.NODE_NAME.getCode() + "'输入有误,请输入以下内容中的其中一个'"
                    + StringUtil.listToString(NodeTypeEnum.getNameList()) + "'");
        }
        receiveNodeImportDto.setType(nodeTypeCode);
        try {
            Date taxDate = DateUtil.strToDate(taxDateStr.replace(".", "-"));
            receiveNodeImportDto.setEndDate(taxDate);
        } catch (Exception e) {
            log.warn("ImportUtil:{}", e.getMessage(), e);
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(taxStartRow) + "'行'"
                    + ProjectImportTitleEnum.TAX_DATE.getCode() + "'请输入yyyy.mm.dd格式的日期");
        }
        String taxAmountStr = getRequiredCellValue(sheet, taxStartRow, ProjectImportTitleEnum.TAX_AMOUNT);
        if (StringUtil.isNumeric(taxAmountStr)) {
            BigDecimal taxAmount = new BigDecimal(taxAmountStr);
            receiveNodeImportDto.setReceivableAmount(BigDecimalUtil.convertYToW(taxAmount));
            receiveNodeImportDto.setCloseAmount(BigDecimalUtil.convertYToW((taxAmount)));
        } else {
            log.warn("ImportUtil.,请输入数字:{}",taxAmountStr);
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(taxStartRow) + "'行'"
                    + ProjectImportTitleEnum.TAX_AMOUNT.getCode() + "'请输入数字");
        }
        return receiveNodeImportDto;
    }

    private static ProjectImportDto getProjectImportDto(Sheet sheet, int startRow) {
        ProjectImportDto projectImportDto = new ProjectImportDto();
        String projectType = getRequiredCellValue(sheet, startRow, ProjectImportTitleEnum.PROJECT_TYPE);
        String projectTypeCode = ProjectTypeEnum.getCode(projectType);
        if (projectType == null) {
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(startRow)+ "'行'"
                    + ProjectImportTitleEnum.PROJECT_TYPE.getCode() + "'输入有误,请输入以下内容中的其中一个'"
                    + StringUtil.listToString(ProjectTypeEnum.getNameList()) + "'");
        }
        if(projectTypeCode.equals(ProjectTypeEnum.DECOR_DESIGN.getCode())){
            projectImportDto.setTaxPoint(DESIGN_TAX_RATE);
        }
        projectImportDto.setProjectType(projectTypeCode);
        String projectStatus = getRequiredCellValue(sheet, startRow, ProjectImportTitleEnum.PROJECT_STATUS);
        Integer projectStatusCode = ProjectStatusEnum.getCode(projectStatus);
        if (projectStatusCode == null) {
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(startRow) + "'行'"
                    + ProjectImportTitleEnum.PROJECT_STATUS.getCode() + "'输入有误,请输入以下内容中的其中一个'"
                    + StringUtil.listToString(ProjectStatusEnum.getNameList()) + "'");
        }
        projectImportDto.setProjectStatus(projectStatusCode);
        String businessType = getRequiredCellValue(sheet, startRow, ProjectImportTitleEnum.BUSINESS_TYPE);
        String businessTypeCode = BusinessTypeEnum.getCode(businessType);
        if (businessTypeCode == null) {
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(startRow)+ "'行'"
                    + ProjectImportTitleEnum.BUSINESS_TYPE.getCode() + "'输入有误,请输入以下内容中的其中一个'"
                    + StringUtil.listToString(BusinessTypeEnum.getNameList()) + "'");
        }
        projectImportDto.setBusinessType(businessTypeCode);
        String contractDateStr = getRequiredCellValue(sheet, startRow, ProjectImportTitleEnum.CONTRACT_DATE);
        try {
            Date contractDate = DateUtil.strToDate(contractDateStr.replace(".", "-"));
            projectImportDto.setContractDate(contractDate);
        } catch (Exception e) {
            log.warn("ImportUtil:{}", e.getMessage(), e);
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(startRow) + "'行'"
                    + ProjectImportTitleEnum.CONTRACT_DATE.getCode() + "'请输入yyyy.mm.dd格式的日期");
        }
        projectImportDto.setProjectName(getRequiredCellValue(sheet, startRow, ProjectImportTitleEnum.PROJECT_NAME));
        projectImportDto.setPartnerName(getRequiredCellValue(sheet, startRow, ProjectImportTitleEnum.PARTNER_NAME));
        projectImportDto.setDataDirector(getCellValue(sheet, ProjectImportTitleEnum.DATA_DIRECTOR, startRow));
        String contractAmountStr = getCellValue(sheet, ProjectImportTitleEnum.CONTRACT_AMOUNT, startRow);
        if (StringUtils.isNotEmpty(contractAmountStr)) {
            if (StringUtil.isNumeric(contractAmountStr)) {
                projectImportDto.setContractAmount(BigDecimalUtil.convertYToW(new BigDecimal(contractAmountStr)));
            }
        }
        String closeAmountStr = getCellValue(sheet, ProjectImportTitleEnum.CLOSE_AMOUNT, startRow);
        if (StringUtils.isNotEmpty(closeAmountStr)) {
            if (StringUtil.isNumeric(closeAmountStr)) {
                projectImportDto.setCloseAmount(BigDecimalUtil.convertYToW(new BigDecimal(closeAmountStr)));
            }
        }
        return projectImportDto;
    }

    private static void validNode(String nodeName, String taxDateS, int startRow) {
        if (StringUtils.isNotEmpty(nodeName) && StringUtils.isEmpty(taxDateS)) {
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(startRow) + "'行有'收款节点'时，'开票日期'必填");
        }
        if (StringUtils.isNotEmpty(taxDateS) && StringUtils.isEmpty(nodeName)) {
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(startRow) + "'行有'开票日期'时，'收款节点'必填");
        }
    }

    private static String getRequiredCellValue(Sheet sheet, int startRow, ProjectImportTitleEnum titleEnum) {
        String cellValue = getCellValue(sheet, titleEnum, startRow);
        validRequiredValue(cellValue, titleEnum, startRow);
        return cellValue;
    }

    private static String getCellValue(Sheet sheet, ProjectImportTitleEnum titleEnum, int startRow) {
        Row row = sheet.getRow(startRow);
        Cell cell = row.getCell(getCol(titleEnum));
        if(cell == null){
            return "";
        }
        return cell.toString().trim();
    }

    /**
     * 解析excel文件获得workbook
     */
    private static Workbook getWorkbook(MultipartFile file) throws IOException {
        //获取文件名称
        String originalFilename = file.getOriginalFilename();
        Workbook workbook = null;
        if (originalFilename.endsWith("xls")) {
            workbook = new HSSFWorkbook(file.getInputStream());
        } else if (originalFilename.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(file.getInputStream());
        }
        return workbook;
    }

    private static void setProjectTitleMap(Row titleRow) {
        if (PROJECT_TITLE_MAP == null) {
            PROJECT_TITLE_MAP = new HashMap<>();
            for (int i = 0; i < PROJECT_TITLE_SIZE; i++) {
                PROJECT_TITLE_MAP.put(titleRow.getCell(i).getStringCellValue(), i);
            }
        }
        log.warn("ImportUtil.setProjectTitleMap,请输入数字:{}",PROJECT_TITLE_MAP);
    }

    private static Integer getCol(ProjectImportTitleEnum titleEnum) {
        Integer col = PROJECT_TITLE_MAP.get(titleEnum.getCode());
        if (col == null) {
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "未找到‘" + titleEnum.getCode() + "’这个标题");
        }
        return col;
    }

    /**
     * 教研必填字段
     *
     * @param value
     */
    private static void validRequiredValue(String value, ProjectImportTitleEnum titleEnum, int row) {
        if (StringUtils.isEmpty(value)) {
            throw new ArmException(ErrorEnum.IMPORT_ERROR, "第'" + getShowRow(row) + "'行'" + titleEnum.getCode() + "'未填");
        }
    }

    private static int getRowSize(Sheet sheet, ProjectImportTitleEnum titleEnum, int row) {
        //为合并单元格
        int sheetMergeCount = sheet.getNumMergedRegions();
        int col = getCol(titleEnum);
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (col >= firstColumn && col <= lastColumn) {
                    return lastRow - firstRow + 1;
                }
            }
        }
        //非合并单元格
        return 1;
    }

    private static int getShowRow(int row){
        return row+1;
    }

}
